using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Types;
using SinBiota.Model.Business.MapServices.Datatypes;
using SinBiota.Model.Business.MapServices.Interfaces;
using SinBiota.Model.Entities;
using SinBiota.Model.Persistence.SessionManager;

namespace SinBiota.Model.Business.MapServices.Implementation
{
    public class MapDatabaseServices : IMapDatabaseServices
    {
        private const int WGS84 = 4326;
        private readonly IConnectionFactory _connectionFactory;
        public MapDatabaseServices(IConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;
        }

        public IList<SqlGeography> GetGeographyList(LatLong nwLatLong, LatLong seLatLong, int zoomLevel, StoredProcedure storedProcedure)
        {
            var connection = _connectionFactory.GetMapConnection();
            var list = new List<SqlGeography>();
            try
            {
                //Open a connection to the database 
                using (connection)
                {
                    connection.Open();
                    //
                    /* New SQL command, to execute the stored procedure */
                    var sqlDataReader = InitAndRunStoredProcedure(nwLatLong, seLatLong, storedProcedure, connection);
                    if (sqlDataReader != null)
                    {
                        while (sqlDataReader.Read())
                        {
                            list.Add((SqlGeography)sqlDataReader["geom"]);
                        }
                        sqlDataReader.Close();
                        sqlDataReader.Dispose();
                    }
                }
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
            return list;
        }

        public IList<GeographyWithIntensity> GetGeographyWithIntensityList(LatLong nwLatLong, LatLong seLatLong, int zoomLevel, StoredProcedure storedProcedure)
        {
            var list = new List<GeographyWithIntensity>();
            var connection = _connectionFactory.GetMapConnection();
            try
            {
                //Open a connection to the database 
                using (connection)
                {
                    connection.Open();
                    var sqlDataReader = InitAndRunStoredProcedure(nwLatLong, seLatLong, storedProcedure, connection);
                    if (sqlDataReader != null)
                    {
                        while (sqlDataReader.Read())
                        {
                            list.Add(new GeographyWithIntensity((SqlGeography)sqlDataReader["geom"], (byte)Math.Round((double)sqlDataReader["intensity"])));
                        }
                        sqlDataReader.Close();
                        sqlDataReader.Dispose();
                    }
                }
                connection.Dispose();
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
            return list;
        }

        private static SqlDataReader InitAndRunStoredProcedure(LatLong nwLatLong, LatLong seLatLong, StoredProcedure storedProcedure, SqlConnection connection)
        {
            /* New SQL command, to execute the stored procedure */
            var cmd = new SqlCommand { Connection = connection, CommandType = CommandType.StoredProcedure };
            //Set SQL Parameters:  
            // 1st: only gets geographies inside this Bounding-Box:
            cmd.Parameters.Add(new SqlParameter("NwLon", nwLatLong.Longitude));
            cmd.Parameters.Add(new SqlParameter("NwLat", nwLatLong.Latitude));
            cmd.Parameters.Add(new SqlParameter("SeLon", seLatLong.Longitude));
            cmd.Parameters.Add(new SqlParameter("SeLat", seLatLong.Latitude));
            // Stored Procedure name and additional parameters:
            cmd.CommandText = storedProcedure.Name;
            foreach (var keyValuePair in storedProcedure.Parameters)
            {
                cmd.Parameters.AddWithValue(keyValuePair.Key, keyValuePair.Value);
            }
            // execute stored proc and return reader:s
            return cmd.ExecuteReader();
        }
        public IList<MapHeatPoint> GetMapHeatPoints(TileLayer tileLayer)
        {
            var heatPoints = new List<MapHeatPoint>();
            //Open a connection to the database 
            var connection = _connectionFactory.GetMapConnection();
            try
            {
                var list = new List<GeographyWithIntensity>();
                using (connection)
                {
                    connection.Open();
                    /* New SQL command, to execute the stored procedure */
                    var cmd = new SqlCommand { Connection = connection, CommandType = CommandType.StoredProcedure };
                    // Stored Procedure     name and additional parameters:
                    var storedProcedure = tileLayer.StoredProcedure;
                    cmd.CommandText = storedProcedure.Name;
                    foreach (var keyValuePair in storedProcedure.Parameters)
                    {
                        cmd.Parameters.AddWithValue(keyValuePair.Key, keyValuePair.Value);
                    }
                    var sqlDataReader = cmd.ExecuteReader();
                    if (sqlDataReader != null)
                    {
                        while (sqlDataReader.Read())
                        {
                            // get center point , intensity and radius:
                            var point = ((SqlGeography)sqlDataReader["center"]).STPointN(1);
                            var intensity = (byte)Math.Round((double)sqlDataReader["intensity"]);
                            var radius = (double)sqlDataReader["radius"];
                            // add heat point
                            heatPoints.Add(new MapHeatPoint(new LatLong((double)point.Lat, (double)point.Long), intensity, radius));
                        }
                        sqlDataReader.Close();
                    }
                }
            }
            finally
            {
                connection.Close();
            }
            return heatPoints;
        }

        public void StoreTileImage(string layerkey, string quadkey, byte[] imageData)
        {
            //Get SQL Server Connection
            var connection = _connectionFactory.GetTileConnection();
            try
            {
                using (connection)
                {
                    connection.Open();

                    var cmd = new SqlCommand("StoreTile", connection) { CommandType = CommandType.StoredProcedure };
                    //parameters
                    cmd.Parameters.AddWithValue("layerkey", layerkey);
                    cmd.Parameters.AddWithValue("quadkey", quadkey);           
                    //image parameter:
                    var paramFileData = cmd.Parameters.Add("@image", SqlDbType.VarBinary);
                    paramFileData.Direction = ParameterDirection.Input;
                    paramFileData.Value = imageData;
                    //execute and close connection
                    cmd.ExecuteNonQuery();
                }
            }
            finally 
            {
               connection.Dispose();
            }
            
        }

        public void BulkStoreTileImages(string layerkey, IDictionary<string, byte[]> quadkeyToimage)
        {
            //Get SQL Server Connection
            var connection = _connectionFactory.GetTileConnection();
            try
            {
                using (connection)
                {
                    connection.Open();
                    foreach (var pair in quadkeyToimage)
                    {
                        var cmd = new SqlCommand("StoreTile", connection) { CommandType = CommandType.StoredProcedure };
                        //parameters
                        cmd.Parameters.AddWithValue("layerkey", layerkey);
                        cmd.Parameters.AddWithValue("quadkey", pair.Key);
                        //image parameter:
                        var paramFileData = cmd.Parameters.Add("@image", SqlDbType.VarBinary);
                        paramFileData.Direction = ParameterDirection.Input;
                        paramFileData.Value = pair.Value;
                        //execute and close connection
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    }
                }
            }
            finally
            {
                connection.Dispose();
            }            
        }
        public byte[] RetrieveTileImage(string layerkey, string quadkey)
        {

            //Get SQL Server Connection
            var connection = _connectionFactory.GetTileConnection();
            try
            {
                using (connection)
                {
                    connection.Open();

                    var cmd = new SqlCommand("RetrieveTile", connection) { CommandType = CommandType.StoredProcedure };
                    //parameters
                    cmd.Parameters.AddWithValue("layerkey", layerkey);
                    cmd.Parameters.AddWithValue("quadkey", quadkey);          
                    // read:
                    var sqlDataReader = cmd.ExecuteReader();
                    if (sqlDataReader == null) return null;
                    sqlDataReader.Read();
                    var response = (byte[])sqlDataReader["image"];
                    cmd.Connection.Close();
                    return response;
                }
            }
            finally
            {
                connection.Dispose();
            }
        }

        public bool TileExists(string layerkey, string quadkey)
        {
            //Get SQL Server Connection
            var connection = _connectionFactory.GetTileConnection();
            try
            {
                using (connection)
                {
                    connection.Open();
                    var cmd = new SqlCommand("TileExists", connection) { CommandType = CommandType.StoredProcedure };
                    //parameters
                    cmd.Parameters.AddWithValue("layerkey", layerkey);
                    cmd.Parameters.AddWithValue("quadkey", quadkey);           
                    var sqlDataReader = cmd.ExecuteScalar();
                    cmd.Connection.Close();
                    return sqlDataReader != null && (int)sqlDataReader > 0;
                }
            }
            finally
            {
                connection.Dispose();
            }            
        }

        public IList<string> RetrieveExistingLayerQuadkeys(string layerkey)
        {
            var quadkeyList = new List<string>();
            //Get SQL Server Connection
            var connection = _connectionFactory.GetTileConnection();
            try
            {
                using (connection)
                {
                    connection.Open();
                    var cmd = new SqlCommand("RetrieveExistingLayerQuadkeys", connection) { CommandType = CommandType.StoredProcedure };
                    //parameters
                    cmd.Parameters.AddWithValue("layerkey", layerkey);
                    // read:
                    var sqlDataReader = cmd.ExecuteReader();
                    if (sqlDataReader != null)
                    {
                        while (sqlDataReader.Read())
                        {
                            quadkeyList.Add((string)sqlDataReader["quadkey"]);
                        }
                        sqlDataReader.Close();
                        sqlDataReader.Dispose();
                    }
                    return quadkeyList;
                }
            }
            finally
            {
                connection.Dispose();
            }
        }

        public void IncludeOccurrencesInSpatialDatabase(IEnumerable<Occurrence> occurrences)
        {
            // build query:
            var sb = new StringBuilder();
            sb.Append("BEGIN TRAN T1;\n");
            foreach (var occurrence in occurrences)
            {
                var lat = occurrence.Location.Latitude;
                var lon = occurrence.Location.Longitude;
                if (lat > 90 | lat < -90) lat = 0;
                if (lon > 90 | lon < -90) lon = 0;
                sb.Append(string.Format("INSERT INTO SpatialOccurrences VALUES({0}, geography::STGeomFromText('POINT({1} {2})', {3}));\n", occurrence.Id, lon, lat, WGS84));
                //geography::STGeomFromText('POINT('+convert(varchar,Longitude)+' '+convert(varchar,Latitude)+')', 4326) 
            }
            sb.Append("COMMIT TRAN T1;");
            // execute
            var connection = _connectionFactory.GetDomainConnection();
            using (connection)
            {
            connection.Open();
            var cmd = new SqlCommand
                          {
                              Connection = connection,
                              CommandType = CommandType.Text,
                              CommandText = sb.ToString()
                          };
            cmd.ExecuteNonQuery();
            }
        }

        public IList<LatLongWithId> GetOccurrencesInSquare(LatLong nw, LatLong se)
        {
            var list = new List<LatLongWithId>();
            //Get SQL Server Connection
            var connection = _connectionFactory.GetDomainConnection();
            try
            {
                using (connection)
                {
                    connection.Open();
                    var cmd = new SqlCommand("GetOccurrencesInSquare", connection) { CommandType = CommandType.StoredProcedure };
                    //parameters
                    var nwlat = nw.Latitude > se.Latitude ? nw.Latitude : se.Latitude;
                    var nwlon = nw.Longitude > se.Longitude ? se.Longitude : nw.Longitude;
                    var selat = nw.Latitude > se.Latitude ? se.Latitude : nw.Latitude;
                    var selon = nw.Longitude > se.Longitude ? nw.Longitude : se.Longitude;
                    cmd.Parameters.AddWithValue("@NwLat", nwlat);
                    cmd.Parameters.AddWithValue("@NwLon", nwlon);
                    cmd.Parameters.AddWithValue("@SeLat", selat);
                    cmd.Parameters.AddWithValue("@SeLon", selon);
                    var sqlDataReader = cmd.ExecuteReader();

                    if (sqlDataReader != null)
                    {
                        while (sqlDataReader.Read())
                        {
                            var p = (SqlGeography) sqlDataReader["geom"];
                            list.Add(new LatLongWithId((double) p.STStartPoint().Lat, (double) p.STStartPoint().Long, (int) sqlDataReader["Id"]));
                        }
                        sqlDataReader.Close();
                        sqlDataReader.Dispose();
                    }
                    cmd.Connection.Close();
                    return list;
                }
            }
            finally
            {
                connection.Dispose();
            }
        }

        public IList<LatLongWithId> GetOccurrencesByTaxon(string taxon)
        {
            var list = new List<LatLongWithId>();
            //Get SQL Server Connection
            var connection = _connectionFactory.GetDomainConnection();
            // query:
            var sql = String.Format("select distinct O.Id, Latitude, Longitude from Location L, TaxonomicClassification TC, Specimen S, Occurrence O WHERE TC.Id = S.TaxonomicClassification_id AND O.Id = S.Occurrence_id AND O.Location_id = L.Id AND TC.LegacyTaxonomicClassification LIKE '%{0}%'", taxon);
            try
            {
                using (connection)
                {
                    connection.Open();
                    var cmd = new SqlCommand() { Connection = connection, CommandText = sql};
                    var sqlDataReader = cmd.ExecuteReader();

                    if (sqlDataReader != null)
                    {
                        while (sqlDataReader.Read())
                        {
                            list.Add(new LatLongWithId((double)sqlDataReader["Latitude"], (double)sqlDataReader["Longitude"], (int)sqlDataReader["Id"]));
                        }
                        sqlDataReader.Close();
                        sqlDataReader.Dispose();
                    }
                    cmd.Connection.Close();
                    return list;
                }
            }
            finally
            {
                connection.Dispose();
            }
            
        }


        public void InitOccurrenceSpatialDatabase()
        {
            const string createAndCopy = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SpatialOccurrences]') AND type in (N'U')) DROP TABLE [dbo].[SpatialOccurrences] SELECT O.Id as Id, geography::STGeomFromText('POINT('+convert(varchar,Longitude)+' '+convert(varchar,Latitude)+')', 4326) as geom INTO SpatialOccurrences from Occurrence O, Location L where O.Location_id = L.Id AND L.Latitude <= 90 AND L.Latitude >= -90";
            const string removeStoredProc = "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetOccurrencesInSquare]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[GetOccurrencesInSquare]";
            const string createStoredProc = "CREATE PROCEDURE [dbo].[GetOccurrencesInSquare] 	@NwLat nvarchar(10),	@NwLon nvarchar(10),	@SeLat nvarchar(10),	@SeLon nvarchar(10) AS BEGIN 	SET NOCOUNT ON; 	DECLARE @BoundingBox geography; 	SET @BoundingBox = geography::STPolyFromText('POLYGON(('+@NwLon+' '+@NwLat+', '+@NwLon+' '+@SeLat+', '+@SeLon+' '+@SeLat+', '+@SeLon+' '+@NwLat+', '+@NwLon+' '+@NwLat+'))', 4326); 	SELECT Id, geom from SpatialOccurrences WHERE (geom.STIntersects(@BoundingBox) = 1) END";
            var connection = _connectionFactory.GetDomainConnection();
            using (connection)
            {
                connection.Open();
                var cmd = new SqlCommand
                {
                    Connection = connection,
                    CommandType = CommandType.Text,
                    CommandText = createAndCopy
                };
                cmd.ExecuteNonQuery();
                cmd = new SqlCommand
                {
                    Connection = connection,
                    CommandType = CommandType.Text,
                    CommandText = removeStoredProc
                };
                cmd.ExecuteNonQuery();
                cmd = new SqlCommand
                {
                    Connection = connection,
                    CommandType = CommandType.Text,
                    CommandText = createStoredProc
                };
                cmd.ExecuteNonQuery();
            }

        }

    }
}